package com.jindun.paper.export;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.jindun.common.StringUtil;

public class ExcelTemplateReporter {

   public static final String LOOP_PARAM = "Each";

   public static final Pattern LOOP_VARIABLE_PATTERN = Pattern.compile("\\$\\{" + LOOP_PARAM + "\\.(\\w+)\\}");

   public static final Pattern SIMPLE_VARIABLE_PATTERN = Pattern.compile("\\$\\{(\\w+)}");

   private int templateCount = 0;
   private int templateRowCount = 0;

   private int tableRowCount = 0;
   private ReportContext context;
   private Workbook workbook;
   private  Sheet sheet;

   public File export(String targetFile, ReportContext context) throws Exception {

      this.init(context);
      this.renderContent();
      return this.write(targetFile);
   }

   public void init( ReportContext context) throws IOException {
      
      InputStream fis = ExcelTemplateReporter.class.getResourceAsStream(context.getTempFileName());
      this.workbook = new HSSFWorkbook(fis);

      this.sheet = workbook.getSheetAt(0);
      
      sheet.setAutobreaks(true); 
      sheet.setPrintGridlines(false);
      sheet.setFitToPage(true);
      
      sheet.getPrintSetup().setLandscape(false);
      sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE ); 
     
      
      
      this.templateCount = context.getEntries().size();
     
     
      int firstRowNum = sheet.getFirstRowNum();
      int lastRowNum = sheet.getLastRowNum();
      this.templateRowCount = (lastRowNum - firstRowNum + context.getTableRowCount() - 1);
      this.tableRowCount = context.getTableRowCount();
      this.context = context;

   }

   private File  write(String fileName) throws IOException {
     
		String path = fileName.substring(0, fileName.lastIndexOf("/"));

		new File(path).mkdirs();
		
		File file = new File(fileName);
		if (!file.exists()) {
			file.createNewFile();
		}

      FileOutputStream out = new FileOutputStream(file);
      workbook.write(out);
      out.close();
      return file;

   }

   private void renderContent() {
      Integer copyRowIndex = this.findNeedCopyRow();
      if (copyRowIndex != null) {
         this.expandTable(copyRowIndex, context.getTableRowCount() - 1);
      }
      this.copyTemplate();
      
      this.replaceSimpleVariable();
      this.replaceLoopVariable();
   }

   private void copyTemplate() {
      int srcBeginRow = 0;
      int srcEndRow = this.templateRowCount - 1;
      int currentRow = srcEndRow;
      sheet.setRowBreak(srcEndRow);
      for (int i = 1; i < this.templateCount; i++) {
         for (int j = srcBeginRow; j <= srcEndRow; j++) {
            Row targetRow = sheet.createRow(++currentRow);
            Row sourceRow = sheet.getRow(j);
            this.copyRow(sourceRow, targetRow);
         }
         if (i < this.templateCount - 1) {
            sheet.setRowBreak(currentRow+1);
         }
      }
      
      
       
      
      for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
         CellRangeAddress region = sheet.getMergedRegion(i);
         if (region.getLastRow() > this.templateRowCount * this.templateCount) {
            break;
         }
         for (int j = 1; j < this.templateCount; j++) {
            int startRow = region.getFirstRow() + j * this.templateRowCount;
            int endRow = region.getLastRow() + j * this.templateRowCount;
           
            CellRangeAddress newRegion = new CellRangeAddress(startRow, endRow, region.getFirstColumn(), region.getLastColumn());
            sheet.addMergedRegion(newRegion);
            
         }
         
      }
      
     
     
      
   }

   private void expandTable(final int rowIndex, final int copyCount) {
      sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), copyCount, true, false);
      Row sourceRow = sheet.getRow(rowIndex);

      for (int i = 1; i <= copyCount; i++) {
         Row targetRow = sheet.createRow(rowIndex + i);
      
         this.copyRow(sourceRow, targetRow);
      }

      for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
         CellRangeAddress region = sheet.getMergedRegion(i);
         if (region.getFirstRow() == rowIndex && region.getLastRow() == rowIndex) {
            for (int j = 1; j <= copyCount; j++) {
               int firstCol = region.getFirstColumn();
               int lastCol = region.getLastColumn();
               int firstRow = region.getFirstRow() + j;
               int lastRow = region.getLastRow() + j;
               CellRangeAddress targetRegion = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
               sheet.addMergedRegion(targetRegion);
            }
         }

      }

   }

   private void copyRow(Row sourceRow, Row targetRow) {
      targetRow.setHeight(sourceRow.getHeight());
      //targetRow.getRowStyle().setHidden(sourceRow.getHeight() < 100 ? true : false);
      //targetRow.setRowStyle(sourceRow.getRowStyle());
      for (int j = sourceRow.getFirstCellNum(); j < sourceRow.getLastCellNum(); j++) {
         Cell sourceCell = sourceRow.getCell(j);
         Cell targetCell = targetRow.createCell(j);
         
         if (targetCell == null || sourceCell == null) {
            continue;
         }
         targetCell.setCellStyle(sourceCell.getCellStyle());
         targetCell.setCellType(sourceCell.getCellType());
         
         switch (sourceCell.getCellType()) {
         case Cell.CELL_TYPE_STRING:
            targetCell.setCellValue(sourceCell.getStringCellValue());
            break;

         case Cell.CELL_TYPE_NUMERIC:
            targetCell.setCellValue(sourceCell.getNumericCellValue());
            break;

         case Cell.CELL_TYPE_FORMULA:
            try {
               targetCell.setCellValue(String.valueOf(sourceCell.getNumericCellValue()));
            } catch (IllegalStateException e) {
               try {
                  targetCell.setCellValue(String.valueOf(sourceCell.getRichStringCellValue()));
               } catch (Exception ex) {
                  targetCell.setCellValue("Error");
               }
            }
            break;
         case Cell.CELL_TYPE_BOOLEAN:
            targetCell.setCellValue(sourceCell.getBooleanCellValue());
            break;

         case Cell.CELL_TYPE_ERROR:
            targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
            break;
         }
      }
   }

   private Integer findNeedCopyRow() {
      int firstRow = sheet.getFirstRowNum();
      int lastRow = sheet.getLastRowNum();

      Integer copyRowIndex = null;
      for (int i = firstRow; i <= lastRow; i++) {
         Row row = sheet.getRow(i);

         int firstCellIndex = row.getFirstCellNum();
         int lastCellIndex = row.getLastCellNum();

         boolean needCopy = false;
         for (int j = firstCellIndex; j <= lastCellIndex; j++) {
            Cell cell = row.getCell(j);
            if (cell != null) {
               String cellText = cell.getStringCellValue();
               if (LOOP_VARIABLE_PATTERN.matcher(cellText).find()) {
                  needCopy = true;
                  break;
               }
            }
         }
         if (needCopy) {
            copyRowIndex = i;
            break;
         }

      }
      return copyRowIndex;

   }

   private void replaceSimpleVariable() {
      
      int currentTemplate = 0;
      for (ReportContext.ContextEntry entry : this.context.getEntries()) {
         int begin = currentTemplate * this.templateRowCount;
         int end = currentTemplate * this.templateRowCount + this.templateRowCount ;
         for (int i = begin; i <= end; i++ ) {
            Row row = sheet.getRow(i);
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {

               Cell cell = row.getCell(j);
               if (cell == null) {
            	   continue;
               }
               String cellText = cell != null ? cell.getStringCellValue() : "";
               Matcher matcher = SIMPLE_VARIABLE_PATTERN.matcher(cellText);
               String newValue  = cellText;
              
               
               while (matcher.find()) {
                  String variableName = matcher.group(1);
                 
                  newValue = newValue.replace(matcher.group(), entry.getValiableValue(variableName));
               }
               cell.setCellValue(StringUtil.nvl(newValue, ""));
            }
            
         }
         currentTemplate++;
        
      }
     
   }

   private void replaceLoopVariable() {
      int currentTemplate = 0;
      for (ReportContext.ContextEntry entry : this.context.getEntries()) {
         int begin = currentTemplate * this.templateRowCount;
         int end = currentTemplate * this.templateRowCount + this.templateRowCount ;
         int loopIndex = 0;
         for (int i = begin; i < end; i++ ) {
            Row row = sheet.getRow(i);
            if (this.isLoopRow(row)) {
               for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                  Cell cell = row.getCell(j);
                  String cellText = cell != null ? cell.getStringCellValue() : "";

                  Matcher matcher = LOOP_VARIABLE_PATTERN.matcher(cellText);
                  if (matcher.find()) {
                     String variableName = matcher.group(1);
                   //  System.out.println(variableName);
                     String newValue = cellText.replace(matcher.group(), entry.getLoopVariableValue(loopIndex, variableName));
                     cell.setCellValue(newValue);
                  }
               }
               loopIndex++;
            }
            
         }
         currentTemplate++;
        
      }
    
   }

   private boolean isLoopRow(Row row) {
      for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
         Cell cell = row.getCell(i);
         String cellText = cell != null ? cell.getStringCellValue() : "";
         if (LOOP_VARIABLE_PATTERN.matcher(cellText).find()) {
            return true;
         }
      }
      return false;

   }

   public static void main(String[] args) throws Exception {
	  
//	  PaperOrder order =  ContextUtil.getBean(IPaperOrderDao.class).findAll().get(0);
//	   
//      ReportContext context = new ExportPaperOrderContext(order);
//      context.init();
//      new ExcelTemplateReporter().export("", "d:\\test.xls", context);
//      System.out.println("Success!");
   }

}
